<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Introduction to SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Introduction to SQLite, databases, SQLite, SQL">
<meta name="description" content="This part of the SQLite tutorial is an introduction to SQLite.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Introduction to SQLite</h1>


<p>
This is SQLite tutorial. It covers the SQLite database engine, sqlite3 command line tool and 
the SQL language covered by the database engine. It is an introductory tutorial
for the beginners. It covers SQLite 3.0 version. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>SQLite database</h2>

<p>
<b>SQLite</b> is an embedded relational database engine. Its developers call it a self-contained, serverless,
zero-configuration and transactional SQL database engine. It is very popular and there are hundreds of millions
copies worldwide in use today. SQLite is used in Solaris 10 and Mac OS operating systems, iPhone or Skype. 
Qt4 library has a buit-in support for the SQLite as well as the Python or the PHP language. Many popular
applications use SQLite internally such as Firefox, Google Chrome or Amarok. 
</p>

<p>
SQLite implements most of the SQL-92 standard for SQL. The SQLite engine is not a standalone process.
Instead, it is statically or dynamically linked into the application. SQLite library has a small size. It could
take less than 300 KiB. 
An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. 
It is a cross platform file. It can be used on various operating systems, both 32 and 64 bit architectures. 
SQLite is created in C programming language and has bindings for many languages like C++, Java, C#, Python,
Perl, Ruby, Visual Basic, Tcl and others. The source code of SQLite is in public domain.
</p>


<h2>Definitions</h2>

<p>
A <b>relational database</b> is a collection of data organized in tables. There are relations among the
tables. The tables are formally described. They consist of rows and columns.  
<b>SQL</b> (Structured Query Language)  is a database computer language designed for managing data
in relational database management systems.

A <b>table</b> is a set of values that is organized using a model of vertical columns and horizontal rows. 
The columns are identified by their names. 

A <b>schema</b> of a database system is its structure described in a formal language. It defines the tables, 
the fields, relationships, views, indexes, procedures, functions, queues, triggers and other elements.

A database <b>row</b> represents a single, implicitly structured data item in a table. It is also called
a tuple or a record. 
A <b>column</b> is a set of data values of a particular simple type, one for each row of the table.
The columns provide the structure according to which the rows are composed.
A <b>field</b> is a single item that exists at the intersection between one row and one column.
A <b> primary key</b> uniquely identifies each record in the table. 
A <b>foreign key</b> is a referential constraint between two tables. The foreign key identifies
a column or a set of columns in one (referencing) table that refers to a column 
or set of columns in another (referenced) table.
A <b>trigger</b> is a procedural code that is automatically executed in response to certain 
events on a particular table in a database. 
A <b>view</b> is a specific look on data in from one or more tables. 
It can arrange data in some specific order, higlight or hide some data.
A view consists of a stored query accessible as a virtual table composed of the result set of a query. 
Unlike ordinary tables a view does not form part of the physical schema. It is a dynamic, 
virtual table computed or collated from data in the database. 

A <b>transaction</b> is an atomic unit of database operations against the data in one or more databases. 
The effects of all the SQL statements in a transaction can be either all committed to the database
or all rolled back. 
<!--Transactions provide an "all-or-nothing" proposition stating that work units performed in 
a database must be completed in their entirety or take no effect whatsoever.-->
An SQL <b>result set</b> is a set of rows from a database, returned by the SELECT statement.
It also contains meta-information about the query such as the column names, and the types and sizes 
of each column as well.
An <b>index</b> is a data structure that improves the speed of data retrieval operations on a database table. 
</p>


<h2>Tables used</h2>
 
<p>
Here we will list all the tables, that are used throughout the tutorial.
The <b>.read</b> command of the sqlite3 tool is used to execute the SQL 
statements from the file. 
</p>

<pre>
sqlite> .read authors.sql
</pre>

<p>
Here we execute the SQL statements located in the authors.sql file.
</p>

<h3>Movies database</h3>

<p>
This is the movies.db database. There are three tables.
Actors, Movies and ActorsMovies.
</p>

<pre class="code">
-- SQL for the Actors table

BEGIN TRANSACTION;
CREATE TABLE Actors(AId integer primary key autoincrement, Name text);
INSERT INTO Actors VALUES(1,'Philip Seymour Hofman');
INSERT INTO Actors VALUES(2,'Kate Shindle');
INSERT INTO Actors VALUES(3,'Kelci Stephenson');
INSERT INTO Actors VALUES(4,'Al Pacino');
INSERT INTO Actors VALUES(5,'Gabrielle Anwar');
INSERT INTO Actors VALUES(6,'Patricia Arquette');
INSERT INTO Actors VALUES(7,'Gabriel Byrne');
INSERT INTO Actors VALUES(8,'Max von Sydow');
INSERT INTO Actors VALUES(9,'Ellen Burstyn');
INSERT INTO Actors VALUES(10,'Jason Miller');
COMMIT;

</pre>

<p>
This is the Actors table.
</p>

<pre class="code">
-- SQL for the Movies table

BEGIN TRANSACTION;
CREATE TABLE Movies(MId integer primary key autoincrement, Title text);
INSERT INTO Movies VALUES(1,'Capote');
INSERT INTO Movies VALUES(2,'Scent of a woman');
INSERT INTO Movies VALUES(3,'Stigmata');
INSERT INTO Movies VALUES(4,'Exorcist');
INSERT INTO Movies VALUES(5,'Hamsun');
COMMIT;

</pre>

<p>
This is the Movies table. 
</p>

<pre class="code">
-- SQL for the ActorsMovies table

BEGIN TRANSACTION;
CREATE TABLE ActorsMovies(Id integer primary key autoincrement, 
                          AId integer, MId integer);
INSERT INTO ActorsMovies VALUES(1,1,1);
INSERT INTO ActorsMovies VALUES(2,2,1);
INSERT INTO ActorsMovies VALUES(3,3,1);
INSERT INTO ActorsMovies VALUES(4,4,2);
INSERT INTO ActorsMovies VALUES(5,5,2);
INSERT INTO ActorsMovies VALUES(6,6,3);
INSERT INTO ActorsMovies VALUES(7,7,3);
INSERT INTO ActorsMovies VALUES(8,8,4);
INSERT INTO ActorsMovies VALUES(9,9,4);
INSERT INTO ActorsMovies VALUES(10,10,4);
INSERT INTO ActorsMovies VALUES(11,8,5);
COMMIT;
</pre>

<p>
This is the ActorsMovies table. 
</p>

<h3>Test database</h3>

<p>
Here we have the tables from the test.db. 
</p>

<pre class="code">
-- SQL for the Cars table

BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Mercedes',57127);
INSERT INTO Cars VALUES(3,'Skoda',9000);
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT INTO Cars VALUES(5,'Bentley',350000);
INSERT INTO Cars VALUES(6,'Citroen',21000);
INSERT INTO Cars VALUES(7,'Hummer',41400);
INSERT INTO Cars VALUES(8,'Volkswagen',21600);
COMMIT;
</pre>

<p>
Cars table.
</p>

<pre class="code">
-- SQL for the orders table

BEGIN TRANSACTION;
CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0), 
                    Customer text);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1200, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(200, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(40, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(1640, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(100, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(50, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(150, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(250, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(840, "Brown");
INSERT INTO Orders(OrderPrice, Customer) VALUES(440, "Black");
INSERT INTO Orders(OrderPrice, Customer) VALUES(20, "Brown");
COMMIT;
</pre>

<p>
Orders table.
</p>

<pre class="code">
-- SQL for the Friends table

BEGIN TRANSACTION;
CREATE TABLE Friends(Id integer PRIMARY KEY, Name text UNIQUE NOT NULL, 
                     Sex text CHECK(Sex IN ('M', 'F')));
INSERT INTO Friends VALUES(1,'Jane', 'F');
INSERT INTO Friends VALUES(2,'Thomas', 'M');
INSERT INTO Friends VALUES(3,'Franklin', 'M');
INSERT INTO Friends VALUES(4,'Elisabeth', 'F');
INSERT INTO Friends VALUES(5,'Mary', 'F');
INSERT INTO Friends VALUES(6,'Lucy', 'F');
INSERT INTO Friends VALUES(7,'Jack', 'M');
COMMIT;
</pre>

<p>
Friends table.
</p>


<pre class="code">
-- SQL for the Customers, Reservations tables

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Customers(CustomerId integer PRIMARY KEY, Name text);
INSERT INTO Customers(Name) VALUES('Paul Novak');
INSERT INTO Customers(Name) VALUES('Terry Neils');
INSERT INTO Customers(Name) VALUES('Jack Fonda');
INSERT INTO Customers(Name) VALUES('Tom Willis');

CREATE TABLE IF NOT EXISTS Reservations(Id integer PRIMARY KEY, 
                                        CustomerId integer, Day text);
INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-22-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-28-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-29-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-29-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(3, '2009-02-12');
COMMIT;
</pre>

<p>
Customers and Reservations.
</p>

<pre class="code">
-- SQL for the Books table

BEGIN TRANSACTION;
CREATE TABLE Books(Id integer PRIMARY KEY, Title text, Author text, 
                   Isbn text default 'not available');
INSERT INTO Books VALUES(1,'War and Peace','Leo Tolstoy','978-0345472403');
INSERT INTO Books VALUES(2,'The Brothers Karamazov',
                         'Fyodor Dostoyevsky','978-0486437910');
INSERT INTO Books VALUES(3,'Crime and Punishment',
                         'Fyodor Dostoyevsky','978-1840224306');
COMMIT;
</pre>

<p>
Books table. 
</p>

<h2>Sources</h2>

<ul>
    <li><a href="http://sqlite.org">sqlite.org</a></li>
    <li><a href="http://wwww.wikipedia.org">wikipedia.org</a></li>
</ul>


<p>
This chapter was an introduction to the SQLite database. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 7, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
